package mappers;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import managers.DatabaseConnection;
import domains.Member;

public class MemberMapper {

	public ArrayList<Member> selectAllMembers() {
		ArrayList<Member> memberInfo = new ArrayList<Member>();
		String sql = "SELECT * FROM hovedopgave.member ORDER BY member_fname";
		try {
			DatabaseConnection dbConnect = new DatabaseConnection();
			PreparedStatement query = dbConnect.getConnect().prepareStatement(
					sql);

			ResultSet rs = query.executeQuery(sql);

			while (rs.next()) {
				Member m = new Member(rs.getInt("member_id"),
						rs.getString("member_fname"),
						rs.getString("member_lname"),
						rs.getString("member_address"),
						rs.getInt("member_zipCode"),
						rs.getString("member_city"),
						rs.getString("member_email"),
						rs.getString("member_phoneNo"),
						rs.getBoolean("member_hasPaid"));

				memberInfo.add(m);
			}

			rs.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return memberInfo;
	}

	public void addMember(Member m) {
		String sql = "insert into hovedopgave.member (member_fname,"
				+ " member_lname, member_address, member_zipCode, member_city,"
				+ "member_email, member_phoneNo) values (?, ?, ?, ?, ?, ?, ?)";
		try {
			DatabaseConnection dbConnect = new DatabaseConnection();
			PreparedStatement query = dbConnect.getConnect().prepareStatement(
					sql);

			query.setString(1, m.getfName());
			query.setString(2, m.getlName());
			query.setString(3, m.getAddress());
			query.setInt(4, m.getZipCode());
			query.setString(5, m.getCity());
			query.setString(6, m.getEmail());
			query.setString(7, m.getPhoneNo());
			query.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void removeMember(int index) {
		String sql = "DELETE FROM hovedopgave.member WHERE member_id = ?";
		try {
			DatabaseConnection dbConnect = new DatabaseConnection();
			PreparedStatement query = dbConnect.getConnect().prepareStatement(sql);
			
			query.setInt(1, index);
			query.executeUpdate();
			
			System.out.println("Record is deleted!");
		} catch(Exception e) {
			System.out.println("Record is not deleted" + e);
		}
	}
	
	public void editMember(Member m, int index) {
		String sql = "UPDATE hovedopgave.member SET member_fname = ?"
				+ ", member_lname = ?, member_address = ?, member_zipCode = ?"
				+ ", member_city = ?, member_email = ?, member_phoneNo = ? WHERE member_id = ?";
		try {
			DatabaseConnection dbConnect = new DatabaseConnection();
			PreparedStatement query = dbConnect.getConnect().prepareStatement(sql);
			
			query.setString(1, m.getfName());
			query.setString(2, m.getlName());
			query.setString(3, m.getAddress());
			query.setInt(4, m.getZipCode());
			query.setString(5, m.getCity());
			query.setString(6, m.getEmail());
			query.setString(7, m.getPhoneNo());
			query.setInt(8, index);
			query.executeUpdate();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public void changeMemberStatus(int index) {
		String sql = "UPDATE hovedopgave.member SET member_hasPaid = 1 WHERE member_id = ?";
		try {
			DatabaseConnection dbConnect = new DatabaseConnection();
			PreparedStatement query = dbConnect.getConnect().prepareStatement(sql);
			query.setInt(1, index);
			query.executeUpdate();
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<Member> selectUnpaidMembers() {
		ArrayList<Member> memberInfo = new ArrayList<Member>();
		String sql = "SELECT * FROM hovedopgave.member WHERE member_hasPaid = 0 ORDER BY member_fname";
		try {
			DatabaseConnection dbConnect = new DatabaseConnection();
			PreparedStatement query = dbConnect.getConnect().prepareStatement(
					sql);

			ResultSet rs = query.executeQuery(sql);

			while (rs.next()) {
				Member m = new Member(rs.getInt("member_id"),
						rs.getString("member_fname"),
						rs.getString("member_lname"),
						rs.getString("member_address"),
						rs.getInt("member_zipCode"),
						rs.getString("member_city"),
						rs.getString("member_email"),
						rs.getString("member_phoneNo"),
						rs.getBoolean("member_hasPaid"));

				memberInfo.add(m);
			}
			rs.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
		return memberInfo;
	}
}

